<?php
class db{
	protected $connection;
	protected $CFG;
	public function __construct(){
		global $CFG;
		$this->CFG=$CFG['db'];
		//使用配置文件内容连接数据库
		$this->connection=new PDO($this->CFG['dsn'],$this->CFG['username'],$this->CFG['password']);
		//设置报错方式为warning
		$this->connection->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_WARNING);
		$this->connection->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY,  true);
		//设置数据库字符编码
		$this->connection->exec("set names {$this->CFG['char_set']}");
		//设置sql_mode为非严格模式
		$this->connection->exec("SET sql_mode=''");

	}
	//插入并返回id insert into article %s 
	public function insert($sql,$data=array()){
		//tablename
		$table=preg_replace("/insert into(?:\s)*(?:`)?([a-z0-9_-]+)(?:`)?(?:\s)*.*/is","\\1",$sql);
		$fds=$this->fields($table);
		//过滤post的数据
		$newdata=array_filter($data,function($v,$k) use ($fds){
			return in_array($k,$fds);
		},ARRAY_FILTER_USE_BOTH);

		//替换%s
		if(strpos($sql,'%s')!==false){
			$str='';
			foreach($newdata as $k=>$v){
				$str.=",{$k}=:{$k}";
			}
			$str='set '.trim($str,',');
			$sql=sprintf($sql,$str);
		}

		$sth=$this->connection->prepare($sql);
		$r=$sth->execute($newdata);
		if($r) return $this->connection->lastInsertId();
		return false;
	}
	//更新 update article %s where itemid=:itemid 
	public function update($sql,$data=[]){
		$table=preg_replace("/update(?:\s)*(?:`)?([a-z0-9_-]+)(?:`)?(?:\s)*.*/is","\\1",$sql);
		$fds=$this->fields($table);
		//过滤post的数据
		$newdata=array_filter($data,function($v,$k) use ($fds){
			return in_array($k,$fds);
		},ARRAY_FILTER_USE_BOTH);

		//替换%s
		if(strpos($sql,'%s')!==false){
			$str='';
			foreach($newdata as $k=>$v){
				$str.=",{$k}=:{$k}";
			}
			$str='set '.trim($str,',');
			$sql=sprintf($sql,$str);
		}

		$sth=$this->connection->prepare($sql);
		return $sth->execute($newdata);
	}
	//删除
	public function delete($sql,$data=[]){
		$sth=$this->connection->prepare($sql);
		return $sth->execute($data);
	}
	//查询多条
	public function select($sql,$data=[]){
		$sth=$this->connection->prepare($sql);
		$sth->execute($data);
		return $sth->fetchAll(PDO::FETCH_ASSOC);
	}
	//查询一条
	public function find($sql,$data=[]){
		$sth=$this->connection->prepare($sql);
		$sth->execute($data);
		return $sth->fetch(PDO::FETCH_ASSOC);
	}
	//查询一列
	public function column($sql,$data=[]){
		$sth=$this->connection->prepare($sql);
		$sth->execute($data);
		//return $sth->fetchAll(PDO::FETCH_COLUMN,0);
		$data=$sth->fetchAll(PDO::FETCH_NUM);
		$r=[];
		foreach($data as $k=>$v){
			if(count($v)>1){
				$r[$v[0]]=$v[1];
			}else{
				$r[]=$v[0];
			}
			
		}
		return $r;
	}
	//查询一个值
	public function value($sql,$data=[]){
		$sth=$this->connection->prepare($sql);
		$sth->execute($data);
		$data=$sth->fetch(PDO::FETCH_NUM);
		return $data[0];
	}
	//获取表的字段
	public function fields($table){
		$fds=$this->select("desc {$table}");
		$data=[];
		foreach($fds as $k=>$v){
			$data[]=$v['Field'];
		}
		return $data;
	}


}